﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PBOffice.Models;

namespace PBOffice.DAL
{
    public class ScheduleService
    {
        public void AddSchedule(Schedule sch)
        {
            string sql = "insert [Schedule] (Title,Address,MeetingId,BeginTime,EndTime,SchContent,CreateUser,CreateTime,IfPrivate) "
            + " values(@Title，@Address，@MeetingId，@BeginTime，@EndTime,@SchContent,@CreateUser,@CreateTime,@IfPrivate)";
            SqlParameter[] spa = {
                                    new SqlParameter("@Title",sch.Title),
                                    new SqlParameter("@Address",sch.Address),
                                    new SqlParameter("@MeetingId",sch.Meeting.MeetingId),
                                    new SqlParameter("@BeginTime",sch.BeginTime),
                                    new SqlParameter("@EndTime",sch.EndTime),
                                    new SqlParameter("@SchContent",sch.SchContent),
                                    new SqlParameter("@CreateUser",sch.CreateUser),
                                    new SqlParameter("@CreateTime",sch.CreateTime),
                                    new SqlParameter("@InPrivate",sch.IfPrivate)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public List<Schedule> GetScheduleByDate(DateTime date)
        {
            string sql = string.Format("select s.*,m.* from Schedule s,MeetingInfo m where m.MeetingId=s.MeetingId and  s.BeginTime='{0}'", date);
            List<Schedule> list = new List<Schedule>();
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Schedule sch = new Schedule();
                    MeetingInfo meet = new MeetingInfo();
                    meet.MeetingId = (int)dr["MeetingId"];
                    meet.MeetingName = dr["MeetingName"].ToString();
                    sch.Address = dr["Address"].ToString();
                    sch.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    sch.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    sch.CreateUser = dr["CreateUser"].ToString();
                    sch.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    sch.IfPrivate = (int)dr["IfPrivate"];
                    sch.Meeting = meet;
                    sch.SchContent = dr["SchContent"].ToString();
                    sch.ScheduleId = (int)dr["ScheduleId"];
                    sch.Title = dr["Title"].ToString();
                    list.Add(sch);

                }
            }
            return list;
        }
        public Schedule GetScheduleById(int id)
        {
            Schedule schedule = new Schedule();
            string sql = string.Format("select s.*,m.* from Schedule s,MeetingInfo m where s.MeetingId=m.MeetingId and s.ScheduleId={0}", id);
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    MeetingInfo meet = new MeetingInfo();
                    meet.MeetingId = (int)dr["MeetingId"];
                    meet.MeetingName = dr["MeetingName"].ToString();
                    schedule.Address = dr["Address"].ToString();
                    schedule.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    schedule.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    schedule.CreateUser = dr["CreateUser"].ToString();
                    schedule.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    schedule.IfPrivate = (int)dr["IfPrivate"];
                    schedule.Meeting = meet;
                    schedule.SchContent = dr["SchContent"].ToString();
                    schedule.ScheduleId = (int)dr["ScheduleId"];
                    schedule.Title = dr["Title"].ToString();
                }
            }
            return schedule;
        }
        public void DeleteSchdeule(int id)
        {
            string sql = string.Format("delete [Schedule] where ScheduleId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void UpdateSchedule(Schedule sch)
        {
            string sql = "insert [Schedule] set Title=@Title,Address=@Address,MeetingId=@MeetingId,BeginTime=@BeginTime,EndTime=@EndTime,"
            + "SchContent=@SchContent,CreateUser=@CreateUser,CreateTime=@CreateTime,IfPrivate=@IfPrivate where ScheduleId=@ScheduleId";
            SqlParameter[] spa = {
                                     new SqlParameter("@Schedule",sch.ScheduleId),
                                    new SqlParameter("@Title",sch.Title),
                                    new SqlParameter("@Address",sch.Address),
                                    new SqlParameter("@MeetingId",sch.Meeting.MeetingId),
                                    new SqlParameter("@BeginTime",sch.BeginTime),
                                    new SqlParameter("@EndTime",sch.EndTime),
                                    new SqlParameter("@SchContent",sch.SchContent),
                                    new SqlParameter("@CreateUser",sch.CreateUser),
                                    new SqlParameter("@CreateTime",sch.CreateTime),
                                    new SqlParameter("@InPrivate",sch.IfPrivate)
                                };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public List<Schedule> GetScheduleByDeptNameCreateTimeName(DateTime begin, DateTime end, string deptName, string name)
        {
            List<Schedule> list = new List<Schedule>();
            string sql = string.Format("select d.DeptName, c.* from Department d,Staff s,Schedule c where d.DeptId=s.DeptId and s.StfName"
            + "=c.CreateUser and  d.DeptName='{0}' and c.CreateUser='{1}' and CreateTime between '{2}' and '{3}'", deptName,name,begin, end );
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Schedule sch = new Schedule();
                    sch.Address = dr["Address"].ToString();
                    sch.BeginTime = Convert.ToDateTime(dr["BeginTime"]);
                    sch.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
                    sch.CreateUser = dr["CreateUser"].ToString();
                    sch.EndTime = Convert.ToDateTime(dr["EndTime"]);
                    sch.IfPrivate = (int)dr["IfPrivate"];
                    // sch.Meeting = meet;
                    sch.SchContent = dr["SchContent"].ToString();
                    sch.ScheduleId = (int)dr["ScheduleId"];
                    sch.Title = dr["Title"].ToString();
                    list.Add(sch);
                }
            }
            return list;
        }
    }
}
